package joo.english.test;

import java.util.ArrayList;

import android.app.Activity;
import android.content.ContentValues;
import android.database.*;
import android.database.sqlite.SQLiteDatabase;
import android.provider.OpenableColumns;
import android.util.Log;
import android.widget.Toast;

public class DAO{
	
	private DTO dto;
	private String sql;
	private final String FNAME="english.db";
	private final String FNAME2="ranking.db";
	private SQLiteDatabase db;
	private SQLiteDatabase db2;
	
	private Cursor cs;
	private ArrayList<DTO> list;	
	
	DAO(){}
	DAO(Activity activity){
		db = activity.openOrCreateDatabase(FNAME, activity.MODE_PRIVATE, null);
		db2 = activity.openOrCreateDatabase(FNAME2, activity.MODE_PRIVATE, null);
	}
	
	ArrayList<DTO> getList(int selectedLevel){
		switch(selectedLevel){
			case 0:
				sql = "select * from DICTIONARY"; break;
			case 1: 
				sql = "select * from DICTIONARY where D_LEVEL=1"; break;
			case 2:
				sql = "select * from DICTIONARY where D_LEVEL=1 OR D_LEVEL=2"; break;
			case 3:
				sql = "select * from DICTIONARY"; break;
			case 4:
				sql = "select * from DICTIONARY where D_BOOKMARK=1"; break;
		}
		Log.i("DB",sql);
		list = new ArrayList<DTO>();
		cs = db.rawQuery(sql, null);
		try{
		while(cs.moveToNext()){
			int idx = cs.getInt(0);
			String word = cs.getString(1);
			String mean = cs.getString(2);
			int level = cs.getInt(3);
			int bookmark = cs.getInt(4);
			int learn = cs.getInt(5);
			
			dto = new DTO(idx, word, mean, level, bookmark,learn);
			
			list.add(dto);
		}
		}catch(SQLException se){
			
		}finally{
			try{
				if(cs != null) cs.close();
				if(db != null) db.close();
				if(db2 != null) db2.close();
			}catch(SQLException se){}
		}
		return list;		
	}
	ArrayList<DTO> getList(int selectedLevel, String index){
		switch(selectedLevel){
			case 0:
				sql = "select * from DICTIONARY where D_WORD like '"+index+"%'"; break;
			case 1: 
				sql = "select * from DICTIONARY where D_WORD like '"+index+"%' and D_LEVEL="+selectedLevel; break;
			case 2:
				sql = "select * from DICTIONARY where D_WORD like '"+index+"%' and D_LEVEL="+selectedLevel; break;
			case 3:
				sql = "select * from DICTIONARY where D_WORD like '"+index+"%' and D_LEVEL="+selectedLevel; break;
			case 4:
				sql = "select * from DICTIONARY where D_WORD like '"+index+"%' and D_BOOKMARK=1"; break;
		}
		
		Log.i("DB",sql);
		list = new ArrayList<DTO>();
		cs = db.rawQuery(sql, null);
		try{
		while(cs.moveToNext()){
			int idx = cs.getInt(0);
			String word = cs.getString(1);
			String mean = cs.getString(2);
			int level = cs.getInt(3);
			int bookmark = cs.getInt(4);
			int learn = cs.getInt(5);
			
			dto = new DTO(idx, word, mean, level, bookmark,learn);
			
			list.add(dto);
		}
		}catch(SQLException se){
			
		}finally{
			try{
				if(cs != null) cs.close();
				if(db != null) db.close();
				if(db2 != null) db2.close();
			}catch(SQLException se){}
		}
		
		return list;		
	}
	
	void updateLearn(DTO dto,int learndata)
	{
		sql = "update DICTIONARY set D_LEARN="+learndata+" where D_IDX="+dto.getIdx();
		Log.i("update D_LEARN",sql);
		db.execSQL(sql);
		if(db != null) db.close();
		if(db2 != null) db2.close();
	}
}
